/**
 * 
 */
package reservationService;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDate;

import constants.Constants;

import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Date;

import encryption.Encrypt;
import reservationEntity.ReservationAddEquipEntity;
import reservationEntity.ReservationEntity;
import userEntity.UserEntity;

/**
 * @author Aakash Singh
 *
 */
public class ReservationService {

	private Connection connection;
	private Connection connection1;
	private PreparedStatement statement;
	private PreparedStatement statement1;

	public ReservationService(){
		try {
			Class.forName(Constants.JDBC_DRIVER);
			connection = DriverManager.getConnection(Constants.DB_URL,Constants.USER_NAME,Constants.PWD);
			connection1 = DriverManager.getConnection(Constants.DB_URL,Constants.USER_NAME,Constants.PWD);
		} 
		catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}


	/**
	 * This method fetches a list of specifications available for a given vehicle class
	 * @param vehicleClass
	 * @return
	 */
	public ResultSet fetchSpecification(String vehicleClass) throws SQLException{
		ResultSet rs = null;
		String query = "select distinct spec from vehicle_specification join vehicle on vehicle=vehicleId where vehicleclass=? ";
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1, vehicleClass);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;

	}

	/**
	 * This method fetches the constants which are a part of fees calculation and stores it in arrayList
	 * @return ArrayList<Double>
	 */
	public ArrayList<Double> getFeeConstants() throws SQLException
	{ ArrayList<Double> fees = new ArrayList<Double>();
	 
	   ResultSet rs = null;
		String query = "select * from feesVariables where feeName = ?";
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1, "Service Fee");
			rs =  statement.executeQuery();
			if(rs.next())
				fees.add(rs.getDouble(2));
			
			
			statement.setString(1, "Age Fee");
			rs =  statement.executeQuery();
			if(rs.next())
				fees.add(rs.getDouble(2));
			
			statement.setString(1, "Tax Percentage");
			rs =  statement.executeQuery();
			if(rs.next())
				fees.add(rs.getDouble(2));
			
			System.out.println("size "+fees.size());
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return fees;
	}
	
	/**
	 * This method fetches a list of vehicle class available for a given vehicle specification
	 * @param specification
	 * @return
	 */
	public ResultSet fetchVehicleClass(String specification){

		return null;
	}

	/**
	 * This method fetches the Vehicle Type which are available for that particular vehicle class on the
	 * estimated pick up date of the reservation.
	 * @param spec
	 * @param vehicleClass
	 * @return
	 * @throws SQLException
	 */
	public ResultSet fetchVehicleType(String spec, String vehicleClass,LocalDate pickupDate,LocalDate returnDate) throws SQLException
	{
		ResultSet rs = null;
		String query = "select distinct (vehicleType) "+
		"from vehicle p "+
		"where 0 < (select ((select count(*) from rental_vehicle rv join vehicle v "+
							" on v.vehicleID=rv.vehicleId join vehicle_specification vs on vs.vehicle=v.vehicleID " +
							"where vehicleClass ='"+vehicleClass+"' and vehicleType=p.vehicleType and spec='"+spec+"' "+
							") "+
							"-"+ 
							"(select count(*) from reservation "+
							"where  NOT (Datediff(pickupdate,'"+returnDate.toString()+"') > 3) and NOT ( Datediff('"+pickupDate.toString()+"',returnDate) > 3 ) "+
							"and vehicleClass ='"+vehicleClass+"' and vehicleType=p.vehicleType  and (status='active' or status='rented') and vehiclespec='"+spec+"' "+
							"))"+ 
					" as possible)";
		
		System.out.println(query);
		try {
			statement = connection.prepareStatement(query);
	/*		statement.setString(1, vehicleClass);
			statement.setString(2, spec);
	*/		rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}

	
	public ResultSet fetchVehicleTypeRent( String vehicleClass) throws SQLException
	{
		ResultSet rs = null;
		String query = "select distinct vehicleType from rental_vehicle rv join vehicle v on v.vehicleID=rv.vehicleId where vehicleclass=? and status='available'";
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1, vehicleClass);

			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}
	
	/**
	 * This method fetches a list of add equip available for a given vehicle class
	 * @param vehicleClass
	 * @return
	 */
	public ResultSet fetchAddEquip(String vehicleClass) throws SQLException{
		ResultSet rs = null;
		String query = "select equipmentName from additional_equipment where vehicleClass = ?";
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1, vehicleClass);
			rs =  statement.executeQuery();
		} 

		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}

	/**
	 * This method fetches all rates for the given vehicle class
	 * @param vehicleClass
	 * @return
	 */
	public ResultSet fetchVehicleClassRate(String vehicleType) throws SQLException{
		ResultSet rs = null;
		String query = "select * from vehicle_rent_cost where vehicleType=?";
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1, vehicleType);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;

	}

	public ResultSet fetchAddEquip(int reservationId){
		System.out.println("db connected");
		ResultSet resultSet = null;
		String query = "select * from reservation_add_equip where reservationName = ?";
		try {
			statement = connection.prepareStatement(query);
			statement.setInt(1, reservationId);
			resultSet =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
		}
		return resultSet;
	}

	
	/**
	 * This method fetches the additional equipment rate for the given additional Equipment list
	 * @param addEquipname
	 * @return
	 */
	public ResultSet fetchAddEquipRate(ArrayList<String> addEquipname){

		ResultSet rs = null;
		if(addEquipname==null)
		{
			rs=null;
		}
		else
		if(addEquipname.size()==2)
		{
			String query = "select dailyRate,hourlyRate from additional_equipment where equipmentName=? or equipmentName=?";
			try {
				statement = connection.prepareStatement(query);
				statement.setString(1, addEquipname.get(0));
				statement.setString(2, addEquipname.get(1));
				rs =  statement.executeQuery();
			} 
			catch (SQLException e1) {
				e1.printStackTrace();
			}	
			
		}
		else
			if(addEquipname.size()==1)
			{
				String query = "select dailyRate,hourlyRate from additional_equipment where equipmentName=?";
				try {
					statement = connection.prepareStatement(query);
					statement.setString(1, addEquipname.get(0));
					rs =  statement.executeQuery();
				} 
				catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		
		return rs;
	}

	
	/**
	 * This method checks the customer if his details are present in the database.
	 * It uses the customer phone number to do the required search details.
	 * @param phoneNumber
	 * @return
	 * @throws SQLException
	 */
	public ResultSet checkMember(String phoneNumber) throws SQLException
	{
		ResultSet rs = null;
		String query = "select * from user where phoneNumber=?";
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1, "+1"+phoneNumber);
			rs =  statement.executeQuery();
		} 


		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}

	
	/**
	 * This method registers the user into the database table.
	 * @pre The customer should not already be present in the table
	 * @post The customer details entry is made in the table
	 * @param us
	 * @return
	 */
	public boolean registerUser(UserEntity us) throws SQLException{

		String query = "INSERT INTO user VALUES (?,?,?,'v5w3b1','customer',?)";
		String query1="INSERT INTO customer VALUES(?,'unregistered',0)";

		try {
			connection.setAutoCommit(false);

			statement = connection.prepareStatement(query);

			statement.setString(1, "+1"+us.getPhoneNumber());
			statement.setString(2, us.getName());
			statement.setString(4, us.getEmailID());
			statement.setString(3, us.getAddress());
			statement.executeUpdate();
			System.out.println("Enter in the registter user");
			statement1=connection1.prepareStatement(query1);
			statement1.setString(1, "+1"+us.getPhoneNumber());
			statement1.executeUpdate();
			connection.commit();
			System.out.println("After commit");

		} 
		catch (SQLException e1) {
			if(connection != null){
				System.err.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					return false;
				}

			}
			e1.printStackTrace();
			return false;
		}
		finally{
			try {
				connection.setAutoCommit(true);	
			}
			catch (SQLException e) {
				e.printStackTrace();
				return false;
			}

		}

		return true;
	}

	
	
	/**
	 * This method stores the reservation detail in the reservation table
	 * @pre All the details are captured which are to be stored in the table
	 * @post The details of the reservation are stored in the database and the revervationID is generated.
	 * @param reservation
	 * @return
	 * @throws Exception 
	 */
	public ResultSet storeReservation(ReservationEntity reservation) throws Exception{

		ResultSet rs = null;
		String query = "INSERT INTO reservation VALUES (null,?,?,?,?,?,?,?,?,?,?,'daily',?,?,?,?,?,?,?);";
		String query1="INSERT INTO reservation_add_equip VALUES (?,?,?)";
		String query2 = "Select last_insert_id() as last_id FROM membership";
		try {
			connection.setAutoCommit(false);

			statement = connection.prepareStatement(query);
			statement.setString(1, "+1"+reservation.getPhoneNumber());
			statement.setString(2, LocalDate.now().toString());
			statement.setString(3, reservation.getPickUpDate().toString());
			statement.setString(4, reservation.getPickUpTime().toString());
			statement.setString(5, reservation.getReturnDate().toString());
			statement.setString(6, reservation.getReturnTime().toString());
			statement.setString(7, reservation.getDriverAge());
			statement.setString(8, reservation.getVehicleType());
			statement.setString(9, reservation.getVehicleClass());
			statement.setString(10, reservation.getVehicleSpec());

			statement.setDouble(11, reservation.getFees());
			statement.setDouble(12, reservation.getRentalCost());
			statement.setDouble(13, reservation.getInsuranceCost());
			statement.setDouble(14, reservation.getEstimatedCost());


			statement.setString(15, Encrypt.encrypt(reservation.getCreditCardNumber()));
			statement.setInt(16, reservation.getExpiryDate());
			statement.setString(17, "active");

			statement.executeUpdate();
			connection.commit();
			statement = connection.prepareStatement(query2);
			rs = statement.executeQuery();
			if(rs.next())
				System.out.println(rs.getInt("last_id"));

			connection1.setAutoCommit(false);
			if(reservation.getAdditionalEquip()!=null)
			{	 statement1=connection1.prepareStatement(query1);
				statement1.setInt(1, rs.getInt("last_id"));
				statement1.setString(2, reservation.getAdditionalEquip().get(0));
				statement1.setDouble(3, reservation.getFees());
				statement1.executeUpdate();
				connection1.commit();
				if(reservation.getAdditionalEquip().size()==2)
				{
					 statement1=connection1.prepareStatement(query1);
						statement1.setInt(1, rs.getInt("last_id"));
						statement1.setString(2, reservation.getAdditionalEquip().get(1));
						statement1.setDouble(3, reservation.getFees());
						statement1.executeUpdate();
						connection1.commit();
				}
				
				
			}
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.err.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			try {
				connection.setAutoCommit(true);
				if(connection1!=null)
					connection1.setAutoCommit(true);
			}
			catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
		}

		return rs;
	}

	/**
	 * This method stores the additional equipment selected for the reservation
	 * @param addEquip
	 */
	public void storeReservationAddEquip(ReservationAddEquipEntity addEquip){

	}

	/**
	 * This method fetches the reservation details of the given reservation ID
	 * @pre The reservation ID is validated 
	 * @post The database details of the reservationId is returned, otherwise null
	 * @param reservationId
	 * @return
	 */
	public ResultSet fetchReservation(long reservationId) throws SQLException{
		ResultSet rs = null;
		String query = "select * from reservation r left outer join reservation_add_equip ra on ra.reservationName=r.reservationID where r.reservationID=? and status='active'";
		try {
			statement = connection.prepareStatement(query);
			statement.setLong(1, reservationId);
			rs =  statement.executeQuery();
			if(rs==null)
			{
				System.out.println("No next in service");
				rs =null;
			}
		//	else 
			//	System.out.println("The data fetched is "+rs.getInt(1));
		} 

		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}

	
	public ResultSet fetchReservationForReturn(int reservationId) throws SQLException{
		ResultSet rs = null;
		String query = "select * from reservation where status='rented' and reservationId = ?";
		try {
			statement = connection.prepareStatement(query);
			statement.setLong(1, reservationId);
			rs =  statement.executeQuery();
			if(rs==null)
			{
				System.out.println("No next in service");
				rs =null;
			}
		//	else 
			//	System.out.println("The data fetched is "+rs.getInt(1));
		} 

		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}
	/**
	 * This method fetches the reservation details for the given phone number and date
	 * @pre Phone number and date are validated
	 * @post The database details of the reservationId is returned, otherwise null
	 * @param phoneNumber,date
	 * @param date
	 * @return
	 */
	public ResultSet fetchReservation(String phoneNumber, String date) throws SQLException{
		ResultSet rs = null;
		String query = "select * from reservation r left outer join reservation_add_equip ra on ra.reservationName=r.reservationID where r.customerPhoneNumber=? and r.pickupdate=?";
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1,"+1"+ phoneNumber);
			statement.setString(2, date);
			rs =  statement.executeQuery();
			//System.out.println("Result set is "+ rs);
			if(rs==null)
			{
				System.out.println("No next in service");
				rs =null;
			}
			//else
				//System.out.println("The data fetched is "+rs.getInt(1));
		} 

		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}

	/**
	 * This method cancels the reservation of the given reservation ID
	 * @pre Reservation ID is validated
	 * @post the reservation pertaining to the reservationID is set to cancelled
	 * @param reservationId
	 */
	public void cancelReservation(int reservationId) throws SQLException{

		String query = "UPDATE reservation SET status='cancelled' where reservationID=?";

		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);	
			statement.setInt(1, reservationId);
			statement.executeUpdate();
			connection.commit();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.err.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}

			}
			e1.printStackTrace();
			throw e1;
		}
		finally
		{	try {
				connection.setAutoCommit(true);	
			}
			catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
		}
		return;
	}

	/**
	 * This method closes the Database connection
	 * 
	 */
	public void closeConnection() {

		try {
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}
			if(statement1 != null){
				statement1.close();
			}
			if(connection1 != null){
				connection1.close();
			}
		} 
		catch(SQLException sqlExec){
			sqlExec.printStackTrace();
		}
	}



}
